package com.yehongyu.mytools.database;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class InsertBlobField {
	private Connection conms=null;
	Statement stmtn=null;
	private Connection conor=null;
	public InsertBlobField() {
		super();
		// TODO Auto-generated constructor stub
	}
	/**
	 * 打开连接，这里用JDBC同时打开两个连接。
	 */
	public boolean openConnection(){
		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver")
					.newInstance();
			String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=egov";
			// egov为数据库
			String user = "sa";
			String password = "sa";
			conms = DriverManager.getConnection(url, user, password);

			Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
			String url2 = "jdbc:oracle:thin:@192.168.70.243:1521:egov";
			// egov为数据库的SID
			String user2 = "eos";
			String password2 = "eos";
			conor = DriverManager.getConnection(url2, user2, password2);
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} 

		// conms = DBConnectionPool.getConnection("mssql");
		if (conms == null) {
			System.out.println(":-( mssql连接失败");
			return false;
		} else
			System.out.println(":-) mssql连接成功");

		// conor = DBConnectionPool.getConnection("oracle");
		if (conor == null) {
			System.out.println(":-( oracle连接失败");
			return false;
		} else
			System.out.println(":-) oracle连接成功");
		try {
			stmtn = conor.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return true;
	}
	/**
	 * 关闭连接
	 */
	public boolean closeConnection(){
		try {
			stmtn.close();
			if (conms != null) {
				conms.close();
			}
			if (conor != null) {
				conor.close();
			}
			return true;
		} catch (SQLException e1) {
			System.out.println("出现异常" + e1.toString());
			// e1.printStackTrace();
			return false;
		}
	}
	/**
	 * 数据转换调用此方法
	 */
	public boolean exchangeData(){
		
		
		String queryAttach="select * from egov.Bak_Biz_LetterAttach ";
		try {
			this.openConnection();
			Statement stmtat=null;
			stmtat = conms.createStatement();
			ResultSet rs = stmtat.executeQuery(queryAttach);
			int no = 0;
			while (rs.next()){
				rs.getInt(1);
				rs.getInt(2);
				rs.getString(3);
				
				InputStream myBString = rs.getBinaryStream(4);
				//根据些回复信件生成子信件和回复信息
				no++;
				//this.insertLetterAttach(tSchema,myBString,"2995",no);
				this.readLetterAttach(myBString,rs.getInt(1),no);
				
			}
			this.closeConnection();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} 	
		
	}
	/**
	 * 读取附件至文件系统
	 * @see PreparedStatement
	 */
	public boolean readLetterAttach(InputStream myBString,int letid,int no){
	   	//String col1 = String.valueOf(tSchema.getLA_ID());
    	int col2 = letid;
    	//String col4 = tSchema.getLA_bAttach();
		try {
			/* 以二进制形式输出 */
			
			if (myBString == null) {
				System.out.println("附件["+no+"]为空！信件( " + col2 + " )输出附件：col3 为空——————————————————————————————————————为空");
			} else {
				System.out.println("附件["+no+"]信件( " + col2 + " )输出附件：col3");
				BufferedOutputStream out = new BufferedOutputStream(
						new FileOutputStream("F:\\attach2\\col3"));
				BufferedInputStream in = new BufferedInputStream(myBString);
				int c;
				while ((c = in.read()) != -1) {
					out.write(c);
				}
				in.close();
				out.close();
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return true;
	}
	/**
	 * 导入附件表
	 * @see PreparedStatement
	 */
	public boolean insertLetterAttach(InputStream myBString,String letid,int no){
	   	String col2 = letid;
    	//String col4 = tSchema.getLA_bAttach();
    	
		String sqlToInsAttach = "insert into EG_LETTER_ATTACHMENT " +
		"values('col1','"+col2+"','col3',empty_blob(),'')";
		System.out.println(sqlToInsAttach);
		try {
		//update_stmt.setObject(4,myBlob);
			conor.setAutoCommit(false);
			int num = stmtn.executeUpdate(sqlToInsAttach);
			System.out.println(num);
			String sqlToUpdAttach = "select ATCH_CONT from EG_LETTER_ATTACHMENT where ATCH_ID='col1' for update";
			ResultSet rs = stmtn.executeQuery(sqlToUpdAttach);
			while (rs.next()) {
                /* 取出此BLOB对象 */
                oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("ATCH_CONT");
                /* 向BLOB对象中写入数据 */
                @SuppressWarnings("deprecation")
				BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
                BufferedInputStream in = new BufferedInputStream(myBString);
                int c;
                while ((c=in.read())!=-1) {
                    out.write(c);
                }
                in.close();
                out.close();
            }
			conor.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return true;
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
		InsertBlobField tData = new InsertBlobField();
		boolean result = tData.exchangeData();
		if (result == true) {
			System.out.println(":-)数据转换成功！");
		} else {
			System.out.println(":-(数据转换失败！");
		}
	}

}
